Relational database systems store data in tables organised by columns and rows. The tables typically are linked together by “relationships” that simplify the storage of the data and make complex queries against the database more efficient. SQL is a standardised language for creating and operating on relational databases. An example of a SQL program is Teradata, a product of NCR Corporation.
In the normal course of running a database, the data in the tables will be queried to retrieve values held in the database. Database queries are also known as read operations (i.e. these operations do not alter the database, they merely retrieve information from the database).
In the present business environment, faster execution of SQL can lead to significant time savings in accessing and processing data stored in the relational databases. To achieve faster execution, each of the SQL query plans are evaluated and query plans with the lowest time cost are chosen. Software programs known as “Optimizers” have been developed to automate this process. Optimizers are typically embedded in the SQL compilers and operate to choose the most cost effective query plans during compilation. An example of an Optimizer program tailored for evaluating SQL query plans based on cost is the “Teradata Optimizer”, also owned by NCR Corporation.
Optimizer programs typically optimize based on either cost (which is typically measured in terms of time), or based on resource consumption. As such, it is imperative that optimization strategies employed by the Optimizer are the most effective for the SQL code to retrieve data from tables either in the shortest period of time possible or using the least amount of resources. After every software release or fix, however, the join strategies and costing algorithms can change considerably. In some applications, these changes may lead to severe degradation in performance.